VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsInsert"
Attribute VB_GlobalNameSpace = True
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit
Private lngTotalRecordSum As Long
Private lngTotalDirectiveRecordSum As Long
Private lngTotalDirectiveRecords As Long
Dim lngTotalCancelRecordsSum As Long
Private lngTotalCancelRecords As Long

Private dblGrossRemSum As Double
Private dblGrossRemPayeSum As Double
Private dblTaxFreeSum As Double
Private dblAssessedTaxAmountSum As Double
Private dblProvisionalTaxAmountSum As Double
Private dblVatAmountSum As Double
Private dblPayeAmountSum As Double
Private dblTaxRateSum As Double

Private Sub ResetFooterTotals()

    dblGrossRemSum = 0
    dblGrossRemPayeSum = 0
    dblTaxFreeSum = 0
    dblAssessedTaxAmountSum = 0
    dblProvisionalTaxAmountSum = 0
    dblVatAmountSum = 0
    dblPayeAmountSum = 0
    dblTaxRateSum = 0
    lngTotalDirectiveRecords = 0
    lngTotalCancelRecords = 0
    

End Sub


Public Function Insert() As Long
'   This function is the main calling function for the insert from rasrs response file to the database
                
    Dim strHeader As String
    Dim strFooter As String
    Dim colRecords As Collection
    Dim colDeclineStrings As Collection
    Dim lngGetFileError As Long
    Dim strConnect As String
        
    On Error GoTo Insert_Fix
    
    Insert = 0
    
    strConnect = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
                
     If objAdoConn.InitConnect(strConnect, "Keytax SQL", "KeyTax") Then
        Do While objFileFunctions.FileDataToInsert(lngGetFileError)
            Set colRecords = New Collection
            Set colDeclineStrings = New Collection
            
            ResetFooterTotals
            ResetRecordVars
            
            If objFileFunctions.ReadFile(strHeader, strFooter, colRecords, colDeclineStrings) Then
                If TokenizeFileString(colRecords, colDeclineStrings) Then
                    If ValidateFooter(strFooter) Then
                        'OldEg_objAdoConn.Connection.BeginTrans
                        objAdoConn.begin
                        If PerformInsert(colRecords) Then
                            If UpdateDeclineReasons(colDeclineStrings) Then
                                If objFileFunctions.FileCleanUp Then
                                    objAdoConn.Commit
                                    lngTotalDirectiveRecordSum = lngTotalDirectiveRecordSum + lngTotalDirectiveRecords
                                    lngTotalCancelRecordsSum = lngTotalCancelRecordsSum + lngTotalCancelRecords
                                    lngTotalRecordSum = lngTotalDirectiveRecordSum + lngTotalCancelRecordsSum
                                    Insert = lngTotalRecordSum
                                Else
                                    objAdoConn.Rollback
                                    objFileFunctions.UpdateLastFileSeq
                                    Insert = FILE_CLEAN_UP_ERROR
                                End If
                            Else
                                objAdoConn.Rollback
                                objFileFunctions.UpdateLastFileSeq
                                Insert = ERROR_UPDATING_DECLINE_REASONS
                            End If
                        Else
                            objAdoConn.Rollback
                            objFileFunctions.UpdateLastFileSeq
                            Insert = ERROR_INSERTING_RECORD
                        End If
                    Else
                        objFileFunctions.UpdateLastFileSeq
                        Insert = ERROR_IN_FOOTER_AGGREGATES
                    End If
                Else
                    objFileFunctions.UpdateLastFileSeq
                    Insert = ERROR_FORMATING_RECORDS
                End If
            Else
              If objReg.ResponseFileType <> "VR" Then
                Insert = ERROR_READING_RECORDS
              End If
            End If
        Loop
    Else
        Insert = DATABASE_CONNECTION_FAILED
    End If

    Exit Function
Insert_Fix:
    HandleError Err.Number, Err.Description, "Insert"
    Insert = UNKNOWN_ERROR_OCCURRED
End Function


Private Function TokenizeDeclRsnString(colTokenizedDecls As Collection, colTokenizedRecs As Collection) As Boolean

    Dim i As Long
    Dim j As Long
    Dim varDeclDescr(4)
    Dim lngDeclRsnCount As Long
    Dim strTemp As String
    Dim colTemp As Collection

    On Error GoTo TokenizeDeclRsnString_Fix
    
    frmShanksOpt.Show
    frmShanksOpt.intCancel = 0
    
    Set colTemp = New Collection
    lngDeclRsnCount = 0
    
    For i = 1 To colTokenizedDecls.Count
    
        strTemp = colTokenizedDecls(i)
                                
        'CHANGES AS PER SARS FOR 5/7/2001
        '1st: 77
        If Len(strTemp) > 46 Then
            If Len(strTemp) <= 111 Then
                If Len(strTemp) <> 111 Then
                    'fill with spaces instead of 0s, as last val is text
                    'strTemp = colTokenizedDecls(i) & String(111 - Len(strTemp), 0)
                    strTemp = Format(colTokenizedDecls(i), "!" & String(111, "@"))
                End If
            Else
                Err.Description = "Invalid Decline Reason record length for record: " & i & ". Length: " & Len(strTemp) & ". Expecting: 111"
                GoTo TokenizeDeclRsnString_Fix
            End If
        Else
            Err.Description = "Invalid Decline Reason record length for record: " & i & ". Length: " & Len(strTemp) & ". Expecting: 111"
            GoTo TokenizeDeclRsnString_Fix
        End If

         'remove record id
        strTemp = Right(strTemp, Len(strTemp) - 1)
        
        varDeclDescr(0) = NumericFormat(Mid(strTemp, 1, 8))
        'REQ-NUM, LEN:8, , NUMERIC, 0 PAD, RIGHT JUST, MAND
        strTemp = Right(strTemp, Len(strTemp) - 8)
        If varDeclDescr(0) = 0 Then
            HandleError Err.Number, Err.Description, "TokenizeDeclRsnString", "Missing REC_NUM in decline record. This field is mandatory.", i
            TokenizeDeclRsnString = False
        End If
        
        varDeclDescr(1) = AlphaFormat(Mid(strTemp, 1, 32))
        'KEY (CORRESPONDS TO A REQ_SEQ_NUM - THIS FIELD IS OPTIONAL!!!), LEN:32, ALPHA , BLANK PADDED, LEFT JUST,
        strTemp = Right(strTemp, Len(strTemp) - 32)
        If Len(varDeclDescr(1)) = 0 Then
            For j = 0 To colTokenizedRecs.Count
                'rec-num-field number=detrecs((this rownum+1)(rec-num field))
                'this rownum-1 - cos theors starts at sec-id, ours starts at 1st det rec (their 2 is our 1)
                varDeclDescr(1) = colTokenizedRecs(CLng(varDeclDescr(0)) - 1)(0)
                Exit For
            Next j
        End If

        varDeclDescr(2) = AlphaFormat(Mid(strTemp, 1, 5))
        'ERROR-NO, LEN:5,  NUMERIC ,MAND
        strTemp = Right(strTemp, Len(strTemp) - 5)
        If varDeclDescr(2) = 0 Then
            HandleError Err.Number, Err.Description, "TokenizeDeclRsnString", "Missing ERROR-NO. This field is mandatory.", i
            TokenizeDeclRsnString = False
        End If
        
        varDeclDescr(3) = AlphaFormat(Mid(strTemp, 1, 65))
        'ERROR-TEXT, LEN:65, ALPHA,
        strTemp = Right(strTemp, Len(strTemp) - 65)

        colTemp.Add varDeclDescr, CStr(i)

        lngDeclRsnCount = lngDeclRsnCount + 1

        If Len(strTemp) <> 0 Then
            HandleError Err.Number, Err.Description, "TokenizeDeclRsnString", "String length error tokenizing Decline Reasons."
            TokenizeDeclRsnString = False
        End If

        DoEvents
        frmShanksOpt.pgbPercent.Value = (i / colTokenizedDecls.Count) * 100
        frmShanksOpt.Label1.Caption = "Extracting from Sars Response File. Record:  " & i & " of : " & colTokenizedDecls.Count
        frmShanksOpt.Refresh

    Next i

    
    If lngDeclRsnCount <> lngDeclineRecordCount Then
        HandleError Err.Number, Err.Description, "TokenizeDeclRsnString", "Error tokenizing Decline Reasons. Invalid Reason count."
        TokenizeDeclRsnString = False
    Else
        Set colTokenizedDecls = Nothing
        Set colTokenizedDecls = New Collection
        Set colTokenizedDecls = colTemp
        TokenizeDeclRsnString = True
    End If
        
    Exit Function
TokenizeDeclRsnString_Fix:
    HandleError Err.Number, Err.Description, "TokenizeDeclRsnString", "Error occurred while tokenizing decline reason string/.", i
   TokenizeDeclRsnString = False
End Function



Private Function TokenizeRecordString(colTokenizedRecs As Collection) As Boolean
'   This function extracts the data from the database, calculates the totals
'   formats each record and saves it to a dynamic array

  
    Dim strTemp As Variant
    Dim strSql As String
    Dim i As Long
    Dim j As Integer
    Dim k As Integer
    Dim varRecords(28)
    Dim dblProvTaxAmt As Double 'needs to be summed *3 here for aggregates
    Dim colTemp As Collection
    
    On Error GoTo TokenizeRecordString_Fix
    
    frmShanksOpt.Show
    frmShanksOpt.intCancel = 0
    
    Set colTemp = New Collection
    
    For i = 1 To colTokenizedRecs.Count
    
        strTemp = colTokenizedRecs(i)
                
        'CHANGES AS PER SARS FOR 5/7/2001
'        If Len(strTemp) = 69 Then
        If Len(strTemp) = 67 Then
            'strTemp = colTokenizedRecs(i) & String(223, "0")
            strTemp = colTokenizedRecs(i) & String(238, "0")
        End If
        
        'remove record id
        strTemp = Right(strTemp, Len(strTemp) - 1)
        
        'CHANGES AS PER SARS FOR 5/7/2001 - THIS CONFLICTS WITH DB
        varRecords(0) = NumericFormat(Mid(strTemp, 1, 20))
        'RespVars.ReqSeqNum(lngTotalDirectiveRecords + 1) = NumericFormat(Mid(strTemp, 1, 20))
        RespVars.ReqSeqNum.Add NumericFormat(Mid(strTemp, 1, 20)), CStr(lngTotalDirectiveRecords + 1)
        'REQ-SEQ_NUM, LEN:20, , NUMERIC ACCORDING TO DB
        strTemp = Right(strTemp, Len(strTemp) - 20)
        
        varRecords(1) = NumericFormat(Mid(strTemp, 1, 15))
        RespVars.ApplId.Add NumericFormat(Mid(strTemp, 1, 15)), CStr(lngTotalDirectiveRecords + 1)
        'APPL_ID, LEN:15, NUMERIC ACCORDING TO DB
        strTemp = Right(strTemp, Len(strTemp) - 15)
        
        varRecords(2) = AlphaFormat(Mid(strTemp, 1, 4))
        RespVars.ItArea.Add AlphaFormat(Mid(strTemp, 1, 4)), CStr(lngTotalDirectiveRecords + 1)
        'IT_AREA, LEN:4:, ALPHA
        strTemp = Right(strTemp, Len(strTemp) - 4)
        
        varRecords(3) = NumericFormat(Mid(strTemp, 1, 10))
        RespVars.ItRefNo.Add NumericFormat(Mid(strTemp, 1, 10)), CStr(lngTotalDirectiveRecords + 1)
        'IT_REF_NO, LEN:10, NUMERIC, BLANKS IF NULL
        strTemp = Right(strTemp, Len(strTemp) - 10)
                
        varRecords(4) = NumericFormat(Mid(strTemp, 1, 15))
        RespVars.DirId.Add NumericFormat(Mid(strTemp, 1, 15)), CStr(lngTotalDirectiveRecords + 1)
        'DIR_ID, LEN:15, BLANKS IF NULL,, NUMERIC ACCORDING TO DB
        strTemp = Right(strTemp, Len(strTemp) - 15)
        
        varRecords(5) = AlphaFormat(Mid(strTemp, 1, 2))
        RespVars.ReqStatus.Add AlphaFormat(Mid(strTemp, 1, 2)), CStr(lngTotalDirectiveRecords + 1)
        'REQ_STATUS, LEN:2, ALPHA, ZERO OR BLANK
        strTemp = Right(strTemp, Len(strTemp) - 2)
        
        'CHANGES AS PER SARS FOR 5/7/2001
'        varRecords(6) = AlphaFormat(Mid(strTemp, 1, 2))
'        'DECL_REASON, LEN:2, COULD BE NULL, ALPHA ACCORDING TO DB
'        strTemp = Right(strTemp, Len(strTemp) - 2)
        
        varRecords(6) = DateFormat(Mid(strTemp, 1, 8))
        RespVars.IssueDate.Add DateFormat(Mid(strTemp, 1, 8)), CStr(lngTotalDirectiveRecords + 1)
        'ISSUE_DATE, LEN:8, CCYYMMDD,
        strTemp = Right(strTemp, Len(strTemp) - 8)
        
        varRecords(7) = DateFormat(Mid(strTemp, 1, 8))
        RespVars.ValidityStart.Add DateFormat(Mid(strTemp, 1, 8)), CStr(lngTotalDirectiveRecords + 1)
        'VALIDITY_START, LEN:8, CCYYMMDD
        strTemp = Right(strTemp, Len(strTemp) - 8)
        
        varRecords(8) = DateFormat(Mid(strTemp, 1, 8))
        RespVars.ValidityEnd.Add DateFormat(Mid(strTemp, 1, 8)), CStr(lngTotalDirectiveRecords + 1)
        'VALIDTY_END, LEN:8, CCYYMMDD
        strTemp = Right(strTemp, Len(strTemp) - 8)
        
        varRecords(9) = NumericFormat(Mid(strTemp, 1, 15), "CENTS")
        RespVars.GrossAmount.Add NumericFormat(Mid(strTemp, 1, 15), "CENTS"), CStr(lngTotalDirectiveRecords + 1)
        'GROSS_AMOUNT, LEN:15, NUMERIC,
        strTemp = Right(strTemp, Len(strTemp) - 15)
        
        varRecords(10) = DateFormat(Mid(strTemp, 1, 8))
        RespVars.AccrualDate.Add DateFormat(Mid(strTemp, 1, 8)), CStr(lngTotalDirectiveRecords + 1)
        'ACCRUAL_DATE, LEN:8, CCYYMMDD
        strTemp = Right(strTemp, Len(strTemp) - 8)
        
        varRecords(11) = NumericFormat(Mid(strTemp, 1, 2))
        RespVars.TaxRate.Add NumericFormat(Mid(strTemp, 1, 2)), CStr(lngTotalDirectiveRecords + 1)
        'TAX_RATE, LEN:2, NUMERIC, BLANK PADDED, RIGHT JUST, OR 0 IF NA
        strTemp = Right(strTemp, Len(strTemp) - 2)
        
        varRecords(12) = NumericFormat(Mid(strTemp, 1, 15), "CENTS")
        RespVars.TaxFreePortion.Add NumericFormat(Mid(strTemp, 1, 15), "CENTS"), CStr(lngTotalDirectiveRecords + 1)
        'TAX_FREE_PORTION, LEN:15, NUMERIC, BLANK PADDED, 0.00, RIGHT JUST,
        strTemp = Right(strTemp, Len(strTemp) - 15)
        
        varRecords(13) = NumericFormat(Mid(strTemp, 1, 15), "CENTS")
        RespVars.GrossAmountPaye.Add NumericFormat(Mid(strTemp, 1, 15), "CENTS"), CStr(lngTotalDirectiveRecords + 1)
        'GROSS_AMOUNT_PAYE, LEN:15, NUMERIC, BLANK PADDED, 0.00, RIGHT JUST,
        strTemp = Right(strTemp, Len(strTemp) - 15)
        
        varRecords(14) = AlphaFormat(Mid(strTemp, 1, 1))
        RespVars.DeductionFrequency.Add AlphaFormat(Mid(strTemp, 1, 1)), CStr(lngTotalDirectiveRecords + 1)
        'DEDUCTION_FREQUENCY, LEN:1, ALPHA, M OY Y
        strTemp = Right(strTemp, Len(strTemp) - 1)
        
        'CHANGES AS PER SARS FOR 5/7/2001
        varRecords(15) = NumericFormat(Mid(strTemp, 1, 15), "CENTS")
        RespVars.AllowedContributions.Add NumericFormat(Mid(strTemp, 1, 15), "CENTS"), CStr(lngTotalDirectiveRecords + 1)
        'ALLOWED-CONTRIBUTIONS, LEN:15, NUMERIC, RIGHT JUST, 0.00, ZERO PAD
        strTemp = Right(strTemp, Len(strTemp) - 15)
                
        'NEW FIELD A PER SARS FOR 28/06/2002
        varRecords(16) = NumericFormat(Mid(strTemp, 1, 13), "RANDS")
        RespVars.ApprovedDeemedRenumeration.Add NumericFormat(Mid(strTemp, 1, 13), "RANDS"), CStr(lngTotalDirectiveRecords + 1)
        'APPROVED-DEEMED-RENUMERATION, LEN:13, NUMERIC, RIGHT JUST, 0.00, ZERO PAD
        strTemp = Right(strTemp, Len(strTemp) - 13)
                
        varRecords(17) = AlphaFormat(Mid(strTemp, 1, 15))
        RespVars.It88RefNo.Add AlphaFormat(Mid(strTemp, 1, 15)), CStr(lngTotalDirectiveRecords + 1)
        'IT-88-REF-NO, LEN:15, ALPHA, BLANK PADDED, LEFT JUST, COULD BR BLANK
        strTemp = Right(strTemp, Len(strTemp) - 15)
        
        varRecords(18) = NumericFormat(Mid(strTemp, 1, 15), "CENTS")
        RespVars.AssessedTaxAmount.Add NumericFormat(Mid(strTemp, 1, 15), "CENTS"), CStr(lngTotalDirectiveRecords + 1)
        'ASSESSED-TAX-AMOUNT, LEN:15, NUMERIC, BLANK PADDED, RIGHT JUST, 0.00, 0 IF NA,
        strTemp = Right(strTemp, Len(strTemp) - 15)
        
        
'        For j = 19 To 21
'        'for k=1 to 3
'            varRecords(j) = NumericFormat(Mid(strTemp, 1, 15), "CENTS")
'            If varRecords(j) <> "NULL" Then
'                dblProvTaxAmt = dblProvTaxAmt + varRecords(j)
'            End If
'            'PROVISIONAL-TAX-AMOUNT, LEN:15, NUMERIC, BLANK PADDED, RIGHT JUST, 0.00, 0 IF NA,
'            strTemp = Right(strTemp, Len(strTemp) - 15)
'        Next j
                
        RespVars.ProvisionalTaxAmount1.Add NumericFormat(Mid(strTemp, 1, 15), "CENTS"), CStr(lngTotalDirectiveRecords + 1)
        If RespVars.ProvisionalTaxAmount1(lngTotalDirectiveRecords + 1) <> "NULL" Then
            dblProvTaxAmt = dblProvTaxAmt + RespVars.ProvisionalTaxAmount1(lngTotalDirectiveRecords + 1)
        End If
        strTemp = Right(strTemp, Len(strTemp) - 15)
        RespVars.ProvisionalTaxAmount2.Add NumericFormat(Mid(strTemp, 1, 15), "CENTS"), CStr(lngTotalDirectiveRecords + 1)
        If RespVars.ProvisionalTaxAmount2(lngTotalDirectiveRecords + 1) <> "NULL" Then
            dblProvTaxAmt = dblProvTaxAmt + RespVars.ProvisionalTaxAmount2(lngTotalDirectiveRecords + 1)
        End If
        strTemp = Right(strTemp, Len(strTemp) - 15)
        RespVars.ProvisionalTaxAmount3.Add NumericFormat(Mid(strTemp, 1, 15), "CENTS"), CStr(lngTotalDirectiveRecords + 1)
        If RespVars.ProvisionalTaxAmount3(lngTotalDirectiveRecords + 1) <> "NULL" Then
            dblProvTaxAmt = dblProvTaxAmt + RespVars.ProvisionalTaxAmount3(lngTotalDirectiveRecords + 1)
        End If
        strTemp = Right(strTemp, Len(strTemp) - 15)
                
'        For j = 22 To 24
'            'As per 05092001 saga - 3rd period not used.
'            ' must be the largest period ie 999999
'            'as per Bronwyn
'            If j <> 24 Then
'                varRecords(j) = NumericFormat(Mid(strTemp, 1, 6))
'            Else
'                varRecords(j) = "999999"
'            End If
'            'PROVISIONAL-TAX-PERIOD, LEN:6, NUMERIC, BLANK PADDED, CCYYNN - NN=TAC PERIOD IE NOT A REAL DATE VALUE
'            strTemp = Right(strTemp, Len(strTemp) - 6)
'        Next j
                
        RespVars.ProvisionalTaxYear1.Add NumericFormat(Mid(strTemp, 1, 6)), CStr(lngTotalDirectiveRecords + 1)
        strTemp = Right(strTemp, Len(strTemp) - 6)
        RespVars.ProvisionalTaxYear2.Add NumericFormat(Mid(strTemp, 1, 6)), CStr(lngTotalDirectiveRecords + 1)
        strTemp = Right(strTemp, Len(strTemp) - 6)
        'RespVars.ProvisionalTaxYear(lngTotalDirectiveRecords + 1) = NumericFormat(Mid(strTemp, 1, 6))
        RespVars.ProvisionalTaxYear3.Add "999999", CStr(lngTotalDirectiveRecords + 1)
        strTemp = Right(strTemp, Len(strTemp) - 6)
                
        varRecords(25) = NumericFormat(Mid(strTemp, 1, 10))
        RespVars.VatRegNo.Add NumericFormat(Mid(strTemp, 1, 10)), CStr(lngTotalDirectiveRecords + 1)
        'VAT-REG-NO, LEN:10, NUMERIC, O IF BLANK
        strTemp = Right(strTemp, Len(strTemp) - 10)
                
        varRecords(26) = NumericFormat(Mid(strTemp, 1, 15), "CENTS")
        RespVars.VatAmount.Add NumericFormat(Mid(strTemp, 1, 15), "CENTS"), CStr(lngTotalDirectiveRecords + 1)
        'VAT-AMOUNT, LEN:15, NUMERIC, BLANK PADDED, O IF NULL
        strTemp = Right(strTemp, Len(strTemp) - 15)
        
        varRecords(27) = NumericFormat(Mid(strTemp, 1, 10))
        RespVars.PayeRefNo.Add NumericFormat(Mid(strTemp, 1, 10)), CStr(lngTotalDirectiveRecords + 1)
        'PAYE-REF-NO, LEN:10, O IF BLANK
        strTemp = Right(strTemp, Len(strTemp) - 10)
        
        varRecords(28) = NumericFormat(Mid(strTemp, 1, 15), "CENTS")
        RespVars.PayeAmount.Add NumericFormat(Mid(strTemp, 1, 15), "CENTS"), CStr(lngTotalDirectiveRecords + 1)
        'PAYE-AMOUNT, LEN:15,NUMERIC, BLANK PADDED, O IF NULL
        strTemp = Right(strTemp, Len(strTemp) - 15)
        
        'Calculate aggregates before converting into string
'        CalculateFooterAggregates varRecords(9), varRecords(11), varRecords(12), _
'                                  varRecords(13), varRecords(18), dblProvTaxAmt, _
'                                  varRecords(26), varRecords(28)
        
        CalculateFooterAggregates RespVars.GrossAmount(lngTotalDirectiveRecords + 1), RespVars.TaxRate(lngTotalDirectiveRecords + 1), RespVars.TaxFreePortion(lngTotalDirectiveRecords + 1), RespVars.GrossAmountPaye(lngTotalDirectiveRecords + 1), _
            RespVars.AssessedTaxAmount(lngTotalDirectiveRecords + 1), dblProvTaxAmt, RespVars.VatAmount(lngTotalDirectiveRecords + 1), RespVars.PayeAmount(lngTotalDirectiveRecords + 1)
        
        If Len(strTemp) <> 0 Then
            MsgBox "Error"
            TokenizeRecordString = False
        Else
            TokenizeRecordString = True
        End If
        
        dblProvTaxAmt = 0
        
        colTemp.Add varRecords, CStr(i)
        
        DoEvents
        frmShanksOpt.pgbPercent.Value = (i / colTokenizedRecs.Count) * 100
        frmShanksOpt.Label1.Caption = "Extracting from Sars Response File. Record:  " & i & " of : " & colTokenizedRecs.Count
        frmShanksOpt.Refresh
        
    Next i
   
   Set colTokenizedRecs = Nothing
   Set colTokenizedRecs = New Collection
   Set colTokenizedRecs = colTemp
   
   Exit Function
TokenizeRecordString_Fix:
   HandleError Err.Number, Err.Description, "TokenizeRecordString", "Error occurred while tokenizing string/calculating aggregates.", i
   TokenizeRecordString = False
End Function

Private Function ValidateDirectiveFooter(strFooter As String) As Boolean

    Dim arFooter(9) As Variant
    Dim strTemp As String
        
    On Error GoTo ValidateDirectiveFooter_Fix
        
    strTemp = strFooter
    
    'remove record id
    strTemp = Right(strTemp, Len(strTemp) - 1)
    
    arFooter(0) = NumericFormat(Mid(strTemp, 1, 8))
    arFooter(0) = IIf(arFooter(0) = "NULL", 0, arFooter(0))
    'REC-NO, LEN:8, JUXT:RIGHT, PADDED:BLANK, NUMERIC, (total no of records in file)
    strTemp = Right(strTemp, Len(strTemp) - 8)
    
    arFooter(1) = NumericFormat(Mid(strTemp, 1, 20), "CENTS")
    arFooter(1) = IIf(arFooter(1) = "NULL", 0, arFooter(1))
    'GROSS-REM-SUM, LEN:20, 0.00, JUXT:RIGHT, PADDEDD:O, NUMERIC,
    '(total of GROSS-REM fields in records)
    strTemp = Right(strTemp, Len(strTemp) - 20)
    
    arFooter(2) = NumericFormat(Mid(strTemp, 1, 20), "CENTS")
    arFooter(2) = IIf(arFooter(2) = "NULL", 0, arFooter(2))
    'GROSS-REM-PAYE-SUM, LEN:20, 0.00, JUXT:RIGHT, PADDEDD:O, NUMERIC,
    '(total of GROSS-REM-SUM fields in records)
    strTemp = Right(strTemp, Len(strTemp) - 20)
    
    arFooter(3) = NumericFormat(Mid(strTemp, 1, 20), "CENTS")
    arFooter(3) = IIf(arFooter(3) = "NULL", 0, arFooter(3))
    'TAX-FREE-SUM, LEN:20, 0.00, JUXT:RIGHT, PADDEDD:O, NUMERIC,
    '(total of TAX-FREE-PORTION fields in records)
    strTemp = Right(strTemp, Len(strTemp) - 20)
    
    arFooter(4) = NumericFormat(Mid(strTemp, 1, 20), "CENTS")
    arFooter(4) = IIf(arFooter(4) = "NULL", 0, arFooter(4))
    'ASSESSED-TAX-AMOUNT-SUM, LEN:20, 0.00, JUXT:RIGHT, PADDEDD:O, NUMERIC,
    '(total of ASSESSED-TAX-AMOUNT fields in records)
    strTemp = Right(strTemp, Len(strTemp) - 20)
    
    arFooter(5) = NumericFormat(Mid(strTemp, 1, 20), "CENTS")
    arFooter(5) = IIf(arFooter(5) = "NULL", 0, arFooter(5))
    'PROVISIONAL-TAX-AMOUNT-SUM, LEN:20, 0.00, JUXT:RIGHT, PADDEDD:O, NUMERIC,
    '(total of PROVISIONAL-TAX-AMOUNT fields in records)
    strTemp = Right(strTemp, Len(strTemp) - 20)
    
    arFooter(6) = NumericFormat(Mid(strTemp, 1, 20), "CENTS")
    arFooter(6) = IIf(arFooter(6) = "NULL", 0, arFooter(6))
    'VAT-AMOUNT-SUM, LEN:20, 0.00, JUXT:RIGHT, PADDEDD:O, NUMERIC,
    '(total of VAT-AMOUNT fields in records)
    strTemp = Right(strTemp, Len(strTemp) - 20)
    
    arFooter(7) = NumericFormat(Mid(strTemp, 1, 20), "CENTS")
    arFooter(7) = IIf(arFooter(7) = "NULL", 0, arFooter(7))
    'PAYE-AMOUNT-SUM, LEN:20, 0.00, JUXT:RIGHT, PADDEDD:O, NUMERIC,
    '(total of PAYE-AMOUNT fields in records)
    strTemp = Right(strTemp, Len(strTemp) - 20)
    
    'barb: this should never have been "CENTS"
    'arFooter(8) = NumericFormat(Mid(strTemp, 1, 16), "CENTS")
    arFooter(8) = NumericFormat(Mid(strTemp, 1, 16))
    arFooter(8) = IIf(arFooter(8) = "NULL", 0, arFooter(8))
    'TAX-RATE-SUM, LEN:16, 0.00, JUXT:RIGHT, PADDEDD:O, NUMERIC,
    '(total of TAX-RATE fields in records)
    strTemp = Right(strTemp, Len(strTemp) - 16)
    
    dblGrossRemSum = Trim(Round(dblGrossRemSum, 2))
    dblGrossRemPayeSum = Trim(Round(dblGrossRemPayeSum, 2))
    dblTaxFreeSum = Trim(Round(dblTaxFreeSum, 2))
    dblAssessedTaxAmountSum = Trim(Round(dblAssessedTaxAmountSum, 2))
    dblProvisionalTaxAmountSum = Trim(Round(dblProvisionalTaxAmountSum, 2))
    dblVatAmountSum = Trim(Round(dblVatAmountSum, 2))
    dblPayeAmountSum = Trim(Round(dblPayeAmountSum, 2))
    dblTaxRateSum = Trim(Round(dblTaxRateSum, 2))
    
    If Trim(CDbl(arFooter(1))) = Trim(dblGrossRemSum) Then
        If Trim(CDbl(arFooter(2))) = Trim(dblGrossRemPayeSum) Then
            If Trim(CDbl(arFooter(3))) = Trim(dblTaxFreeSum) Then
                If Trim(CDbl(arFooter(4))) = Trim(dblAssessedTaxAmountSum) Then
                    If Trim(CDbl(arFooter(5))) = Trim(dblProvisionalTaxAmountSum) Then
                        If Trim(CDbl(arFooter(6))) = Trim(dblVatAmountSum) Then
                            If Trim(CDbl(arFooter(7))) = Trim(dblPayeAmountSum) Then
                                If Trim(CDbl(arFooter(8))) = Trim(dblTaxRateSum) Then
                                    If Trim(CDbl(arFooter(0))) = (lngTotalDirectiveRecords + lngDeclineRecordCount) Then
                                        ValidateDirectiveFooter = True
                                    Else
                                        HandleError Err.Number, Err.Description, "Calculate Aggregates error (lngTotalRecords + lngDeclineRecordCount)."
                                        ValidateDirectiveFooter = False
                                    End If
                                Else
                                    HandleError Err.Number, Err.Description, "Calculate Aggregates error (dblTaxRateSum)."
                                    ValidateDirectiveFooter = False
                                End If
                            Else
                                HandleError Err.Number, Err.Description, "Calculate Aggregates error (dblPayeAmountSum)."
                                ValidateDirectiveFooter = False
                            End If
                        Else
                            HandleError Err.Number, Err.Description, "Calculate Aggregates error (dblVatAmountSum)."
                            ValidateDirectiveFooter = False
                        End If
                    Else
                        HandleError Err.Number, Err.Description, "Calculate Aggregates error (dblProvisionalTaxAmountSum)."
                        ValidateDirectiveFooter = False
                    End If
                Else
                    HandleError Err.Number, Err.Description, "Calculate Aggregates error (dblAssessedTaxAmountSum)."
                    ValidateDirectiveFooter = False
                End If
            Else
                HandleError Err.Number, Err.Description, "Calculate Aggregates error (dblTaxFreeSum)."
                ValidateDirectiveFooter = False
            End If
        Else
            HandleError Err.Number, Err.Description, "Calculate Aggregates error (dblGrossRemPayeSum)."
            ValidateDirectiveFooter = False
        End If
    Else
        HandleError Err.Number, Err.Description, "Calculate Aggregates error (dblGrossRemSum)."
        ValidateDirectiveFooter = False
    End If
                
    Exit Function
ValidateDirectiveFooter_Fix:
    HandleError Err.Number, Err.Description, "ValidateDirectiveFooter"
    ValidateDirectiveFooter = False
End Function
Private Sub CalculateFooterAggregates(ByVal GrossAmount As Variant, _
                                      ByVal TaxRate As Variant, _
                                      ByVal TaxFreePortion As Variant, _
                                      ByVal GrossAmountPaye As Variant, _
                                      ByVal AssessedTaxAmount As Variant, _
                                      ByVal ProvisionalTaxAmount As Variant, _
                                      ByVal VatAmount As Variant, _
                                      ByVal PayeAmount As Variant)
   
   On Error GoTo CalculateFooterAggregates_Fix
   
   lngTotalDirectiveRecords = lngTotalDirectiveRecords + 1
   
    If IsNull(GrossAmount) Then
        GrossAmount = 0
    End If
    If GrossAmount = "NULL" Then
        GrossAmount = 0
    End If
    GrossAmount = CDbl(GrossAmount)
    
    If IsNull(TaxRate) Then
        TaxRate = 0
    End If
    If TaxRate = "NULL" Then
        TaxRate = 0
    End If
    TaxRate = CDbl(TaxRate)
   
    If IsNull(TaxFreePortion) Then
        TaxFreePortion = 0
    End If
    If TaxFreePortion = "NULL" Then
        TaxFreePortion = 0
    End If
    TaxFreePortion = CDbl(TaxFreePortion)
   
    If IsNull(GrossAmountPaye) Then
        GrossAmountPaye = 0
    End If
    If GrossAmountPaye = "NULL" Then
        GrossAmountPaye = 0
    End If
    GrossAmountPaye = CDbl(GrossAmountPaye)
    
    If IsNull(AssessedTaxAmount) Then
        AssessedTaxAmount = 0
    End If
    If AssessedTaxAmount = "NULL" Then
        AssessedTaxAmount = 0
    End If
    AssessedTaxAmount = CDbl(AssessedTaxAmount)
    
    If IsNull(ProvisionalTaxAmount) Then
        ProvisionalTaxAmount = 0
    End If
    If ProvisionalTaxAmount = "NULL" Then
        ProvisionalTaxAmount = 0
    End If
    ProvisionalTaxAmount = CDbl(ProvisionalTaxAmount)
    
    If IsNull(VatAmount) Then
        VatAmount = 0
    End If
    If VatAmount = "NULL" Then
        VatAmount = 0
    End If
    VatAmount = CDbl(VatAmount)
    
    If IsNull(PayeAmount) Then
        PayeAmount = 0
    End If
    If PayeAmount = "NULL" Then
        PayeAmount = 0
    End If
    PayeAmount = CDbl(PayeAmount)
   
    'Calculating aggregates
    dblGrossRemSum = dblGrossRemSum + GrossAmount
    dblTaxRateSum = dblTaxRateSum + TaxRate
    dblTaxFreeSum = dblTaxFreeSum + TaxFreePortion
    dblGrossRemPayeSum = dblGrossRemPayeSum + GrossAmountPaye
    dblAssessedTaxAmountSum = dblAssessedTaxAmountSum + AssessedTaxAmount
    dblProvisionalTaxAmountSum = dblProvisionalTaxAmountSum + ProvisionalTaxAmount
    dblVatAmountSum = dblVatAmountSum + VatAmount
    dblPayeAmountSum = dblPayeAmountSum + PayeAmount
    
    Exit Sub

CalculateFooterAggregates_Fix:
    HandleError Err.Number, Err.Description, "CalculateFooterAggregates", , lngTotalDirectiveRecords
End Sub

Private Function AlphaFormat(Preformat As Variant) As String
'   Coverrts data to alpha format
'   Will convert blanks to NULL

    Dim strTemp As String
    Dim intPos As Integer
    
    If Preformat = 0 Then
        AlphaFormat = "NULL"
    Else
    
        strTemp = Preformat
        'REMOVE ALL APOSTRAPHE'S TO AVOID ERRORS ON SQL STMTS
        intPos = InStr(1, strTemp, "'")
        If intPos <> 0 Then
            Do While intPos <> 0
                strTemp = Mid(strTemp, 1, intPos - 1) & Mid(strTemp, intPos + 1)
                intPos = InStr(1, strTemp, "'")
            Loop
        End If
    
        'REMOVE ALL COMMAS TO AVOID ERRORS ON SQL STMTS
        intPos = InStr(1, strTemp, ",")
        If intPos <> 0 Then
            Do While intPos <> 0
                strTemp = Mid(strTemp, 1, intPos - 1) & Mid(strTemp, intPos + 1)
                intPos = InStr(1, strTemp, ",")
            Loop
        End If
                
        AlphaFormat = "'" & Trim(strTemp) & "'"
    End If
End Function
Private Function DateFormat(Preformat As Variant) As String

    Dim strTemp As String
    Dim dtDate As Date
    Dim strSqlDateFormat As String
    Dim strNewFormat As String
    Dim strYYYY As String
    Dim strMM As String
    Dim strDD As String
    
    If Preformat = 0 Then
        DateFormat = "NULL"
    Else
        strTemp = Trim(Preformat)
        If Len(strTemp) > 4 Then
            'Migrating Conversion Functions from Oracle to SQL Server:
            'Character strings are converted to dates. In Oracle, the default date format model is DD-MON-YY.
            'If you use any other format, you must provide an appropriate date format model.
            'The CAST and CONVERT functions convert standard date formats automatically, without the need for
            'a format model.
            
            'all dates will be 8 long CCYYMMDD eg 21081975 - as per sars documentation
            
            strYYYY = Left(strTemp, 4)
            strMM = Mid(strTemp, 5, 2)
            strDD = Right(strTemp, 2)
            'Shane: Code changed 2003/03/26. Inserting date incorrectly into DB
'            strNewFormat = strDD & "/" & strMM & "/" & strYYYY
'            strTemp = "'" & Format(strNewFormat, "DD-MMM-YYYY") & "'"
'            strSqlDateFormat = " (CONVERT(datetime," & strTemp & ", 5)) "
'            DateFormat = strSqlDateFormat '"insert into bm_test (tdate) values" & strSqlDateFormat
            'Shane: New code - 2003/03/26.
            strNewFormat = strYYYY & "/" & strMM & "/" & strDD
            strTemp = "'" & Format(strNewFormat, "YYYY-MM-DD") & "'"
            DateFormat = strTemp
            
        Else
            strTemp = "'" & strTemp & "'"
        End If
    End If
End Function
Private Function NumericFormat(Preformat As Variant, Optional strType As String) As String

'    Dim lngCents As Long
'    Dim lngRands As Long
'    Dim lngValue As Long
    
    Dim lngCents As Double
    Dim lngRands As Double
    Dim lngValue As Double
    
    If Len(Trim(Preformat)) = 0 Then
        Preformat = 0
    End If
    
    'Preformat = CLng(Preformat)
    
    If Preformat = 0 Then
        NumericFormat = "NULL"
    ElseIf UCase(strType) = "CENTS" Then
'        lngCents = CStr(Right(Preformat, 2))
'        lngRands = CStr(Left(Preformat, Len(Preformat) - 2))
'        'lngValue = CLng(lngRands) & "." & lngCents
'        NumericFormat = lngRands & "." & lngCents
        NumericFormat = Preformat / 100
    Else
        lngValue = Preformat 'to get rid of padding
        NumericFormat = lngValue
    End If

End Function


Private Function UpdateDeclineReasons(colDeclines As Collection) As Boolean

    Dim lngRecordsAffected As Long
    Dim strSql As String
    Dim rsExistingReference As ADODB.Recordset
    Dim i As Long
    Dim j As Integer

    Dim arFileData As Variant

    On Error GoTo UpdateDeclineReasons_Fix

    frmShanksOpt.Show
    frmShanksOpt.intCancel = 0

    For i = 1 To colDeclines.Count
    
        'UPDATE THE RELEVANT REQ-SEQ-NUM WITH DECL RSN
        'bjm0509: changes as per SARS 05/07/2001 - sending in multiple decl rsns per request
        
        'check for a matching response
        strSql = "Select REQ_SEQ_NUM from DIR_RESPONSE where REQ_SEQ_NUM = " & colDeclines(i)(1)
        Set rsExistingReference = objAdoConn.openrecordset(strSql)
        If rsExistingReference.RecordCount > 0 Then
        
            'insert decline code for req_seq_num if not already there
            strSql = "Select REQ_SEQ_NUM from DIR_DECLINE_REASONS"
            strSql = strSql & " where REQ_SEQ_NUM = " & colDeclines(i)(1)
            strSql = strSql & " and DECLINE_REASON_CODE = " & colDeclines(i)(2)
            strSql = strSql & " and DECLINE_REASON_TEXT = " & colDeclines(i)(3)
            Set rsExistingReference = objAdoConn.openrecordset(strSql)
            If rsExistingReference.RecordCount <= 0 Then
                
                strSql = "Insert into DIR_DECLINE_REASONS (REQ_SEQ_NUM, DECLINE_REASON_CODE, DECLINE_REASON_TEXT)"
                strSql = strSql & " values(" & colDeclines(i)(1) & "," & colDeclines(i)(2) & "," & colDeclines(i)(3) & ")"
                lngRecordsAffected = objAdoConn.executequery(strSql)
                If lngRecordsAffected <> 1 Then
                    UpdateDeclineReasons = False
                    Exit Function
                End If
            Else
'===============================================================================================================================================================================================================================================
                'New code added by Shane on 2002/12/17
                'If the decline record already exists - write to temp table
                'This is to accomodate the fact that SARS keep on sending us duplicate dir response data
                strSql = "Insert into DIR_DECLINE_REASONS_TEMP (REQ_SEQ_NUM, DECLINE_REASON_CODE, DECLINE_REASON_TEXT,RES_FILE_NAME)"
                strSql = strSql & " values(" & colDeclines(i)(1) & "," & colDeclines(i)(2) & "," & colDeclines(i)(3) & ","
                strSql = strSql & "'" & objReg.AsciiFileName & "')"
                lngRecordsAffected = objAdoConn.executequery(strSql)
                If lngRecordsAffected <> 1 Then
                    UpdateDeclineReasons = False
                    Exit Function
                Else
                    UpdateDeclineReasons = True
                End If
'===============================================================================================================================================================================================================================================
            End If
        Else
            HandleError Err.Number, Err.Description, "UpdateDeclineReasons", "No matching Response for this Decline Reason. Req_seq_num: " & colDeclines(i)(0), i
            UpdateDeclineReasons = False
            Exit Function

        End If

        DoEvents
        frmShanksOpt.pgbPercent.Value = (i / colDeclines.Count) * 100
        frmShanksOpt.Label1.Caption = "Inserting Decline Reason Response detail into database. Record:  " & i & " of : " & colDeclines.Count
        frmShanksOpt.Refresh
        
    Next i

    Unload frmShanksOpt

    UpdateDeclineReasons = True
    
    Exit Function
UpdateDeclineReasons_Fix:
    HandleError Err.Number, Err.Description, "UpdateDeclineReasons", "Failed to insert record.", i
    UpdateDeclineReasons = False
End Function


Public Function PerformInsert(colRecordData As Collection) As Boolean

    Select Case objReg.ResponseFileType
    
        Case "DR"
            'If PerformDirectiveInsert(colRecordData) Then
            If PerformDirectiveInsert2 Then
                PerformInsert = True
            Else
                PerformInsert = False
            End If
        Case "CR"
            If PerformCancelUpdate(colRecordData) Then
                PerformInsert = True
            Else
                PerformInsert = False
            End If
        Case Else
            PerformInsert = False
    End Select
    
End Function
'Public Function PerformDirectiveInsert(colRecordData As Collection) As Boolean
'
'    Dim lngRecordsAffected As Long
'    Dim strSql As String
'    Dim rsExistingReference As ADODB.Recordset
'    Dim i As Long
'    Dim j As Integer
'
'  Dim arFileData As Variant
'
'    On Error GoTo PerformDirectiveInsert_Fix
'    'bjm0509:
'    'IF GET IT-REF NO THAT DOESN'T EXIST FOR THIS REQ_SEQ_NO
'    'IN DIR_REQUEST, THEN ADD IT (UPDATE)
'
'    frmShanksOpt.Show
'    frmShanksOpt.intCancel = 0
'
'    For i = 1 To colRecordData.Count
'
'        'strSql = "Select REQ_SEQ_NUM from DIR_REQUEST_FORMS where REQ_SEQ_NUM = " & colRecordData(i)(0)
'        strSql = "Select REQ_SEQ_NUM from DIR_REQUEST where REQ_SEQ_NUM = " & colRecordData(i)(0)
'        Set rsExistingReference = objAdoConn.openrecordset(strSql)
'
'        If rsExistingReference.RecordCount > 0 Then
'            If Not IsNull(rsExistingReference(0)) Then
'                'if there's already a response for this seq no ignore it
'                strSql = "Select * from dir_response where req_seq_num = '" & colRecordData(i)(0) & "'"
'                Set rsExistingReference = objAdoConn.openrecordset(strSql)
'                If rsExistingReference.RecordCount = 0 Then
'                    'If IsNull(rsExistingReference(0)) Then
'                        strSql = "Insert into DIR_RESPONSE ("
'                        strSql = strSql & " REQ_SEQ_NUM,"
'                        strSql = strSql & " REQUEST_STATUS,"
'                        strSql = strSql & " EMAILED_YN,"
'                        strSql = strSql & " SARS_APPL_ID,"
'                        strSql = strSql & " INCOME_TAX_AREA_CODE,"
'                        strSql = strSql & " DIRECTIVE_NUMBER,"
'                        'CHANGES AS PER SARS FOR 5/7/2001
'                        'strSql = strSql & " DECLINE_REASON,"
'                        strSql = strSql & " ISSUE_DATE,"
'                        strSql = strSql & " DIR_START_DATE,"
'                        strSql = strSql & " DIR_END_DATE,"
'                        strSql = strSql & " TAX_PERCENTAGE,"
'                        strSql = strSql & " TAX_FREE_PORTION,"
'                        strSql = strSql & " GROSS_AMOUNT_PAYE,"
'                        strSql = strSql & " DEDUCTION_FREQUENCY,"
'                        'CHANGES AS PER SARS FOR 5/7/2001
'                        strSql = strSql & " ALLOWED_CONTRIB,"
'                        strSql = strSql & " VAT_REGISTRATION_NUMBER,"
'                        strSql = strSql & " VAT_AMOUNT,"
'                        strSql = strSql & " PAYE_AMT,"
'                        strSql = strSql & " ASSESSED_TAX_AMT,"
'                        strSql = strSql & " IT88_REF_NO,"
'                        'CHANGES FOR SARS FOR 28/06/2002
'                        strSql = strSql & " APPROVED_DEEMED_RENUMERATION,"
'                        strSql = strSql & " SCOD_DIR_TYPE,"
'                        strSql = strSql & " CREATED_DATE,"
'                        strSql = strSql & " CREATED_BY)"
'                        strSql = strSql & " values("
'                        strSql = strSql & colRecordData(i)(0) & ", "
'                        strSql = strSql & colRecordData(i)(5) & ", "
'                        strSql = strSql & "'N', "
'                        strSql = strSql & colRecordData(i)(1) & ", "
'                        strSql = strSql & colRecordData(i)(2) & ", "
'                        strSql = strSql & colRecordData(i)(4) & ", "
'                        strSql = strSql & colRecordData(i)(6) & ", "
'                        strSql = strSql & colRecordData(i)(7) & ", "
'                        strSql = strSql & colRecordData(i)(8) & ", "
'                        'barb: huh? - this was (9=gross_amt)
'                        'strSql = strSql & colRecordData(i)(9) & ", "
'                        'strSql = strSql & colRecordData(i)(12) & ", "
'
'                        strSql = strSql & colRecordData(i)(11) & ", "
'                        strSql = strSql & colRecordData(i)(12) & ", "
'
'                        strSql = strSql & colRecordData(i)(13) & ", "
'                        strSql = strSql & colRecordData(i)(14) & ", "
'                        strSql = strSql & colRecordData(i)(15) & ", "
'                        strSql = strSql & colRecordData(i)(25) & ", "
'                        strSql = strSql & colRecordData(i)(26) & ", "
'                        strSql = strSql & colRecordData(i)(28) & ","
'                        strSql = strSql & colRecordData(i)(18) & ","
'                        strSql = strSql & colRecordData(i)(17) & ","
'                        strSql = strSql & colRecordData(i)(16) & ","
'                        strSql = strSql & "'LS',"
'                        strSql = strSql & " GETDATE(),"
'                        strSql = strSql & " 'TkInsert')"
'
'                        'objAdoConn.Connection.BeginTrans
'                        lngRecordsAffected = objAdoConn.executequery(strSql)
'                        If lngRecordsAffected <> 1 Then
'                            'objAdoConn.Connection.RollbackTrans
'                            PerformDirectiveInsert = False
'                            Exit Function
'                        Else
'
'DoUpdates:
'                            'objAdoConn.Connection.BeginTrans
'
'                            For j = 18 To 20
'                                If colRecordData(i)(j) <> 0 Then
'                                    If colRecordData(i)(j) <> "NULL" Then
'                                        strSql = "Insert into DIR_PROVISIONAL_TAX "
'                                        strSql = strSql & " (REQ_SEQ_NUM, TAX_PERIOD, TAX_AMOUNT)"
'                                        strSql = strSql & " values(" & colRecordData(i)(0) & ", " & colRecordData(i)(j + 3) & ", " & colRecordData(i)(j) & ")"
'                                        lngRecordsAffected = objAdoConn.executequery(strSql)
'                                        If lngRecordsAffected <> 1 Then
'                                            'objAdoConn.Connection.RollbackTrans
'                                            PerformDirectiveInsert = False
'                                            Exit Function
'                                        Else
'                                            PerformDirectiveInsert = True
'                                        End If
'                                    End If
'                                End If
'                        Next j
'                        If UpdateDir_Request_FormsStatus(colRecordData(i)(0), colRecordData(i)(16), i) Then
'                            PerformDirectiveInsert = True
'                            'objAdoConn.Connection.CommitTrans
'                        Else
'                            PerformDirectiveInsert = False
'                            Exit Function
'                        End If
'                End If
'            Else
'                'if there's already a response for this seq no ignore it
'                PerformDirectiveInsert = True
'            End If
'        Else
'            HandleError Err.Number, Err.Description, "PerformDirectiveInsert", "No matching request for this response. Req_seq_num: " & colRecordData(i)(0), i
'            PerformDirectiveInsert = False
'            Exit Function
'        End If
'    Else
'        HandleError Err.Number, Err.Description, "PerformDirectiveInsert", "No matching request for this response. Req_seq_num: " & colRecordData(i)(0), i
'        PerformDirectiveInsert = False
'        Exit Function
'    End If
'
'        DoEvents
'        frmShanksOpt.pgbPercent.Value = (i / colRecordData.Count) * 100
'        frmShanksOpt.Label1.Caption = "Inserting Response detail into database. Record:  " & i & " of : " & colRecordData.Count
'        frmShanksOpt.Refresh
'
'Next i
'
'    Unload frmShanksOpt
'
'    Exit Function
'PerformDirectiveInsert_Fix:
'    HandleError Err.Number, Err.Description, "PerformDirectiveInsert", "Failed to insert record.", i
'    PerformDirectiveInsert = False
'End Function
Private Function UpdateDir_Request_FormsStatus(Req_Seq_No As Variant, IncTaxRefNo As Variant, RecLineNo As Long) As Boolean

'   Updates scod status from Captured to Extracted and saves
'   extracted date

    Dim strSql As String
    Dim lngRecordsAffected As Long
    Dim rsItRefNo As ADODB.Recordset
    
    On Error GoTo UpdateDir_Request_FormsStatus_Fix
    
    If IncTaxRefNo <> "NULL" And IncTaxRefNo <> "''" Then
            strSql = "Select INCOME_TAX_REFERENCE_NUMBER from DIR_REQUEST where REQ_SEQ_NUM = '" & Req_Seq_No & "'"
            Set rsItRefNo = objAdoConn.openrecordset(strSql)
            If IsNull(rsItRefNo!INCOME_TAX_REFERENCE_NUMBER) Then
              strSql = "Update DIR_REQUEST"
              strSql = strSql & " set SCOD_PROCESS_STATUS = 'RS' ,"
              strSql = strSql & " PROCESS_STATUS_DATE = GETDATE(),"
              strSql = strSql & " INCOME_TAX_REFERENCE_NUMBER = " & IncTaxRefNo & ","
              strSql = strSql & " RES_FILE_NAME = '" & objReg.AsciiFileName & "'"
              strSql = strSql & " where REQ_SEQ_NUM = '" & Req_Seq_No & "'"
            Else
              strSql = "Update DIR_REQUEST"
              strSql = strSql & " set SCOD_PROCESS_STATUS = 'RS' ,"
              strSql = strSql & " PROCESS_STATUS_DATE = GETDATE() ,"
              strSql = strSql & " RES_FILE_NAME = '" & objReg.AsciiFileName & "'"
              strSql = strSql & " where REQ_SEQ_NUM = '" & Req_Seq_No & "'"
            End If
    Else
        strSql = "Update DIR_REQUEST"
        strSql = strSql & " set SCOD_PROCESS_STATUS = 'RS' ,"
        strSql = strSql & " PROCESS_STATUS_DATE = GETDATE() ,"
        strSql = strSql & " RES_FILE_NAME = '" & objReg.AsciiFileName & "'"
        strSql = strSql & " where REQ_SEQ_NUM = '" & Req_Seq_No & "'"
    End If
    lngRecordsAffected = objAdoConn.executequery(strSql)
    If lngRecordsAffected <> 1 Then
        HandleError Err.Number, Err.Description, "UpdateDir_Request_FormsStatus", "Update Dir_Request(SCOD_PROCESS_STATUS) failed.  Insert failed on REQ_SEQ_NUM: " & Req_Seq_No & "."
        UpdateDir_Request_FormsStatus = False
    Else
        UpdateDir_Request_FormsStatus = True
    End If
        
Exit Function

UpdateDir_Request_FormsStatus_Fix:
    HandleError Err.Number, Err.Description, "UpdateDir_Request_FormsStatus", "All Inserts/Updates for this record will be rolled back.", RecLineNo
    UpdateDir_Request_FormsStatus = False
End Function

Private Sub Class_Initialize()
  If objReg Is Nothing Then
    Set objReg = New clsRegistry
  End If
  If objAdoConn Is Nothing Then
    'Set objAdoConn = New clsAdoConnection
    Set objAdoConn = CreateObject("AdoDbserver.clsDbFunctions")
  End If
  If objFileFunctions Is Nothing Then
    Set objFileFunctions = New clsFileFunctions
  End If
End Sub
Private Sub Class_Terminate()
  Set objReg = Nothing
  Set objAdoConn = Nothing
  Set objFileFunctions = Nothing
End Sub
Public Function GetErrorMessage(ErrorNumber As Long) As String
    
    Dim strMsg As String

    If ErrorNumber > 0 Then
        strMsg = lngTotalDirectiveRecordSum & " Directive records were inserted." & vbCrLf
        strMsg = strMsg & lngTotalCancelRecordsSum & " Cancel records were inserted." & vbCrLf
        strMsg = strMsg & ErrorNumber & " Records in total were inserted into the database."
        GetErrorMessage = strMsg
    Else
        Select Case ErrorNumber
            Case 0
                GetErrorMessage = "NO_DATA_TO_INSERT "
            Case -1
                GetErrorMessage = "DATABASE_CONNECTION_FAILED "
            Case -2
                GetErrorMessage = "FILE_ERROR "
            Case -3
                GetErrorMessage = "ERR_IN_FILE_SEQ_NO "
            Case -4
                GetErrorMessage = "ERROR_CREATING_HEADER_RECORD "
            Case -5
                GetErrorMessage = "ERROR_CREATING_FOOTER_RECORD "
            Case -6
                GetErrorMessage = "ERROR_READING_RECORDS "
            Case -7
              GetErrorMessage = "ERROR_FORMATING_RECORDS"
            Case -8
              GetErrorMessage = "ERROR_INSERTING_RECORD"
            Case -9
              GetErrorMessage = "FILE_CLEAN_UP_ERROR"
            Case -10
              GetErrorMessage = "UNKNOWN_ERROR_OCCURRED"
            Case -11
              GetErrorMessage = "RESPONSE_FILE_LOCATION_UNKNOWN"
        End Select
    End If

End Function

Private Function TokenizeFileString(colRecords As Collection, colDeclines As Collection) As Boolean

    Select Case objReg.ResponseFileType

        Case "VR"
            If TokenizeDeclRsnString(colDeclines, colRecords) Then
                TokenizeFileString = True
            Else
                TokenizeFileString = False
            End If
        Case "DR"
            If TokenizeRecordString(colRecords) Then
                If colDeclines.Count > 0 Then
                    If TokenizeDeclRsnString(colDeclines, colRecords) Then
                        TokenizeFileString = True
                    Else
                        TokenizeFileString = False
                    End If
                Else
                    TokenizeFileString = True
                End If
            Else
                TokenizeFileString = False
            End If
        Case "CR"
            If TokenizeCancelRecordString(colRecords) Then
                TokenizeFileString = True
            Else
                TokenizeFileString = False
            End If
        Case Else
            TokenizeFileString = False
    End Select

End Function


Private Function TokenizeCancelRecordString(colFixedString As Collection) As Boolean
'   This function extracts the data from the database, calculates the totals
'   formats each record and saves it to a dynamic array

  
    Dim strTemp As Variant
    Dim strSql As String
    Dim i As Long
    Dim varRecords(6)
    Dim colTemp As Collection
    
    On Error GoTo TokenizeCancelRecordString_Fix
    
    frmShanksOpt.Show
    frmShanksOpt.intCancel = 0
            
    Set colTemp = New Collection
    
    For i = 1 To colFixedString.Count
    
        strTemp = colFixedString(i)
                
        If Len(strTemp) = 50 Then
            'strTemp = colFixedString(i) & String(223, "0")
            strTemp = colFixedString(i) & Space(253)
        End If
        
        'remove record id
        strTemp = Right(strTemp, Len(strTemp) - 1)
        
        varRecords(0) = NumericFormat(Mid(strTemp, 1, 20))
        'REQ-SEQ_NUM, LEN:20, , NUMERIC ACCORDING TO DB
        strTemp = Right(strTemp, Len(strTemp) - 20)
        
         varRecords(1) = NumericFormat(Mid(strTemp, 1, 15))
        'DIR_ID, LEN:15, BLANKS IF NULL,, NUMERIC ACCORDING TO DB
        strTemp = Right(strTemp, Len(strTemp) - 15)
        
        varRecords(2) = NumericFormat(Mid(strTemp, 1, 10))
        'IT_REF_NO, LEN:10, NUMERIC, BLANKS IF NULL
        strTemp = Right(strTemp, Len(strTemp) - 10)
        
        varRecords(3) = AlphaFormat(Mid(strTemp, 1, 2))
        'DIR_STATUS, LEN:2, ALPHA, ZERO OR BLANK
        strTemp = Right(strTemp, Len(strTemp) - 2)
        
        varRecords(4) = AlphaFormat(Mid(strTemp, 1, 2))
        'RESPONSE, LEN:2, ALPHA, ZERO OR BLANK
        strTemp = Right(strTemp, Len(strTemp) - 2)
        
        varRecords(5) = AlphaFormat(Mid(strTemp, 1, 253))
        'RESPONSE-TEXT, LEN:253, ALPHA, ZERO OR BLANK
        strTemp = Right(strTemp, Len(strTemp) - 253)
        
        lngTotalCancelRecords = lngTotalCancelRecords + 1
        
        If Len(strTemp) <> 0 Then
            MsgBox "Error"
            TokenizeCancelRecordString = False
        Else
            TokenizeCancelRecordString = True
        End If
                
        colTemp.Add varRecords, CStr(i)
        
        DoEvents
        frmShanksOpt.pgbPercent.Value = (i / colFixedString.Count) * 100
        frmShanksOpt.Label1.Caption = "Extracting from Sars Response File. Record:  " & i & " of : " & colFixedString.Count
        frmShanksOpt.Refresh
        
    Next i
   
   Set colFixedString = Nothing
   Set colFixedString = New Collection
   Set colFixedString = colTemp
   
   Exit Function
TokenizeCancelRecordString_Fix:
   HandleError Err.Number, Err.Description, "TokenizeCancelRecordString", "Error occurred while tokenizing string/calculating aggregates.", i
   TokenizeCancelRecordString = False
End Function
Private Function ValidateCancelFooter(strFooter As String) As Boolean

    Dim arFooter(9) As Variant
    Dim strTemp As String
        
    On Error GoTo ValidateCancelFooter_Fix
        
    strTemp = strFooter
    
    'remove record id
    strTemp = Right(strTemp, Len(strTemp) - 1)
    
    arFooter(0) = NumericFormat(Mid(strTemp, 1, 8))
    arFooter(0) = IIf(arFooter(0) = "NULL", 0, arFooter(0))
    'REC-NO, LEN:8, JUXT:RIGHT, PADDED:BLANK, NUMERIC, (total no of records in file)
    strTemp = Right(strTemp, Len(strTemp) - 8)

    If Trim(CDbl(arFooter(0))) = lngTotalCancelRecords Then
        ValidateCancelFooter = True
    Else
        HandleError Err.Number, Err.Description, "Calculate Aggregates error."
        ValidateCancelFooter = False
    End If
                
    Exit Function
ValidateCancelFooter_Fix:
    HandleError Err.Number, Err.Description, "ValidateCancelFooter"
    ValidateCancelFooter = False
End Function
Private Function ValidateFooter(strFooter As String) As Boolean

    Select Case objReg.ResponseFileType

    Case "DR"
        If ValidateDirectiveFooter(strFooter) Then
            ValidateFooter = True
        Else
            ValidateFooter = False
        End If
    Case "CR"
        If ValidateCancelFooter(strFooter) Then
            ValidateFooter = True
        Else
            ValidateFooter = False
        End If
    Case Else
        ValidateFooter = False
    End Select
End Function
Public Function PerformCancelUpdate(colFileData As Collection) As Boolean

    Dim lngRecordsAffected As Long
    Dim strSql As String
    Dim rsExistingReference As ADODB.Recordset
    Dim i As Long
    Dim j As Integer

  Dim arFileData As Variant

    On Error GoTo PerformCancelUpdate_Fix

    For i = 1 To colFileData.Count
    strSql = "Select REQ_SEQ_NUM from DIR_REQUEST where REQ_SEQ_NUM = " & colFileData(i)(0)
        Set rsExistingReference = objAdoConn.openrecordset(strSql)
        If rsExistingReference.RecordCount > 0 Then
            If Not IsNull(rsExistingReference(0)) Then
                strSql = "Update DIR_REQUEST"
                strSql = strSql & " set SCOD_PROCESS_STATUS = 'RS' ,"
                strSql = strSql & " PROCESS_STATUS_DATE = GETDATE() ,"
                strSql = strSql & " RES_FILE_NAME = '" & objReg.AsciiFileName & "'"
                strSql = strSql & " where REQ_SEQ_NUM = " & colFileData(i)(0)
                            
                lngRecordsAffected = objAdoConn.executequery(strSql)
                If lngRecordsAffected <> 1 Then
                    HandleError 0, "Update Dir_Request(SCOD_PROCESS_STATUS) failed.", "GetRecords", "Insert failed on REQ_SEQ_NUM: " & colFileData(i)(0) & "."
                    PerformCancelUpdate = False
                Else
                    strSql = "Update DIR_RESPONSE"
                    strSql = strSql & " set CANCEL_PROCESS_STATUS = 'RS' ,"
                    strSql = strSql & " CANCEL_STATUS = " & colFileData(i)(4) & " ,"
                    strSql = strSql & " REQUEST_STATUS = " & colFileData(i)(3) & " ,"
                    strSql = strSql & " EMAILED_YN = 'N'"
                    strSql = strSql & " where REQ_SEQ_NUM = " & colFileData(i)(0)
                    lngRecordsAffected = objAdoConn.executequery(strSql)
                    If lngRecordsAffected <> 1 Then
                        HandleError 0, "Update Dir_Request(SCOD_PROCESS_STATUS) failed.", "GetRecords", "Update DIR_RESPONSE failed on REQ_SEQ_NUM: " & colFileData(i)(0) & "."
                        PerformCancelUpdate = False
                    Else
                        PerformCancelUpdate = True
                    End If
                End If
            Else
                HandleError 0, "No matching request for this response.", "PerformCancelUpdate", "Req_seq_num: " & colFileData(i)(0), i
                PerformCancelUpdate = False
            End If
        Else
            HandleError 0, "No matching request for this response.", "PerformCancelUpdate", "Req_seq_num: " & colFileData(i)(0), i
            PerformCancelUpdate = False
        End If



        DoEvents
        frmShanksOpt.pgbPercent.Value = (i / colFileData.Count) * 100
        frmShanksOpt.Label1.Caption = "Inserting Response detail into database. Record:  " & i & " of : " & colFileData.Count
        frmShanksOpt.Refresh
        
    Next i

    Unload frmShanksOpt

    Exit Function
PerformCancelUpdate_Fix:
    HandleError Err.Number, Err.Description, "PerformCancelUpdate", "Failed to insert record.", i
    PerformCancelUpdate = False
End Function
Public Function PerformDirectiveInsert2() As Boolean

    Dim lngRecordsAffected As Long
    Dim strSql As String
    Dim rsExistingReference As ADODB.Recordset
    Dim i As Long
    Dim j As Integer
    Dim intDeleteAttempt As Integer

  Dim arFileData As Variant

    On Error GoTo PerformDirectiveInsert2_Fix
    'bjm0509:
    'IF GET IT-REF NO THAT DOESN'T EXIST FOR THIS REQ_SEQ_NO
    'IN DIR_REQUEST, THEN ADD IT (UPDATE)

    frmShanksOpt.Show
    frmShanksOpt.intCancel = 0

    'For i = 1 To colRecordData.Count
    For i = 1 To RespVars.FileResponseCount
    
        intDeleteAttempt = 0
    
        'strSql = "Select REQ_SEQ_NUM from DIR_REQUEST where REQ_SEQ_NUM = " & respvars.reqseqnum
        strSql = "Select REQ_SEQ_NUM from DIR_REQUEST where REQ_SEQ_NUM = " & RespVars.ReqSeqNum(i)
        Set rsExistingReference = objAdoConn.openrecordset(strSql)
        
        If rsExistingReference.RecordCount > 0 Then
            If Not IsNull(rsExistingReference(0)) Then
InsertResponse:
                strSql = "Select * from dir_response where req_seq_num = '" & RespVars.ReqSeqNum(i) & "'"
                Set rsExistingReference = objAdoConn.openrecordset(strSql)
                If rsExistingReference.RecordCount = 0 Then
                    'If IsNull(rsExistingReference(0)) Then
                        strSql = "Insert into DIR_RESPONSE ("
                        strSql = strSql & " REQ_SEQ_NUM,"
                        strSql = strSql & " REQUEST_STATUS,"
                        strSql = strSql & " EMAILED_YN,"
                        strSql = strSql & " SARS_APPL_ID,"
                        strSql = strSql & " INCOME_TAX_AREA_CODE,"
                        strSql = strSql & " DIRECTIVE_NUMBER,"
                        'CHANGES AS PER SARS FOR 5/7/2001
                        'strSql = strSql & " DECLINE_REASON,"
                        strSql = strSql & " ISSUE_DATE,"
                        strSql = strSql & " DIR_START_DATE,"
                        strSql = strSql & " DIR_END_DATE,"
                        strSql = strSql & " TAX_PERCENTAGE,"
                        strSql = strSql & " TAX_FREE_PORTION,"
                        strSql = strSql & " GROSS_AMOUNT_PAYE,"
                        strSql = strSql & " DEDUCTION_FREQUENCY,"
                        'CHANGES AS PER SARS FOR 5/7/2001
                        strSql = strSql & " ALLOWED_CONTRIB,"
                        strSql = strSql & " VAT_REGISTRATION_NUMBER,"
                        strSql = strSql & " VAT_AMOUNT,"
                        strSql = strSql & " PAYE_AMT,"
                        strSql = strSql & " ASSESSED_TAX_AMT,"
                        strSql = strSql & " IT88_REF_NO,"
                        'CHANGES FOR SARS FOR 28/06/2002
                        strSql = strSql & " APPROVED_DEEMED_RENUMERATION,"
                        strSql = strSql & " SCOD_DIR_TYPE,"
                        strSql = strSql & " CREATED_DATE,"
                        strSql = strSql & " CREATED_BY)"
                        strSql = strSql & " values("
                        strSql = strSql & RespVars.ReqSeqNum(i) & ", "
                        strSql = strSql & RespVars.ReqStatus(i) & ", "
                        strSql = strSql & "'N', "
                        strSql = strSql & RespVars.ApplId(i) & ", "
                        strSql = strSql & RespVars.ItArea(i) & ", "
                        strSql = strSql & RespVars.DirId(i) & ", "
                        strSql = strSql & RespVars.IssueDate(i) & ", "
                        strSql = strSql & RespVars.ValidityStart(i) & ", "
                        strSql = strSql & RespVars.ValidityEnd(i) & ", "
                        strSql = strSql & RespVars.TaxRate(i) & ", "
                        strSql = strSql & RespVars.TaxFreePortion(i) & ", "
                        strSql = strSql & RespVars.GrossAmountPaye(i) & ", "
                        strSql = strSql & RespVars.DeductionFrequency(i) & ", "
                        strSql = strSql & RespVars.AllowedContributions(i) & ", "
                        strSql = strSql & RespVars.VatRegNo(i) & ", "
                        strSql = strSql & RespVars.VatAmount(i) & ", "
                        strSql = strSql & RespVars.PayeAmount(i) & ","
                        strSql = strSql & RespVars.AssessedTaxAmount(i) & ","
                        strSql = strSql & RespVars.It88RefNo(i) & ","
                        strSql = strSql & RespVars.ApprovedDeemedRenumeration(i) & ","
                        strSql = strSql & "'LS',"
                        strSql = strSql & " GETDATE(),"
                        strSql = strSql & " 'TkInsert')"
                        
                        'objAdoConn.Connection.BeginTrans
                        lngRecordsAffected = objAdoConn.executequery(strSql)
                        If lngRecordsAffected <> 1 Then
                            'objAdoConn.Connection.RollbackTrans
                            PerformDirectiveInsert2 = False
                            Exit Function
                        Else
                        
DoUpdates:
                            'objAdoConn.Connection.BeginTrans
                        
'                        For j = 18 To 20
'                            If colRecordData(i)(j) <> 0 Then
'                                If colRecordData(i)(j) <> "NULL" Then
'                                    strSql = "Insert into DIR_PROVISIONAL_TAX "
'                                    strSql = strSql & " (REQ_SEQ_NUM, TAX_PERIOD, TAX_AMOUNT)"
'                                    strSql = strSql & " values(" & colRecordData(i)(0) & ", " & colRecordData(i)(j + 3) & ", " & colRecordData(i)(j) & ")"
'                                    lngRecordsAffected = objAdoConn.executequery(strSql)
'                                    If lngRecordsAffected <> 1 Then
'                                        'objAdoConn.Connection.RollbackTrans
'                                        PerformDirectiveInsert2 = False
'                                        Exit Function
'                                    Else
'                                        PerformDirectiveInsert2 = True
'                                    End If
'                                End If
'                            End If
'                        Next j
                        
                        'check if any provisional tax info to insert
                        If RespVars.ProvisionalTaxAmount1(i) <> 0 Then
                            If RespVars.ProvisionalTaxAmount1(i) <> "NULL" Then
                                strSql = "Insert into DIR_PROVISIONAL_TAX "
                                strSql = strSql & " (REQ_SEQ_NUM, TAX_PERIOD, TAX_AMOUNT)"
                                strSql = strSql & " values(" & RespVars.ReqSeqNum(i) & ", " & RespVars.ProvisionalTaxYear1(i) & ", " & RespVars.ProvisionalTaxAmount1(i) & ")"
                                lngRecordsAffected = objAdoConn.executequery(strSql)
                                If lngRecordsAffected <> 1 Then
                                    'objAdoConn.Connection.RollbackTrans
                                    PerformDirectiveInsert2 = False
                                    Exit Function
                                Else
                                    PerformDirectiveInsert2 = True
                                End If
                            End If
                        End If
                        If RespVars.ProvisionalTaxAmount2(i) <> 0 Then
                            If RespVars.ProvisionalTaxAmount2(i) <> "NULL" Then
                                strSql = "Insert into DIR_PROVISIONAL_TAX "
                                strSql = strSql & " (REQ_SEQ_NUM, TAX_PERIOD, TAX_AMOUNT)"
                                strSql = strSql & " values(" & RespVars.ReqSeqNum(i) & ", " & RespVars.ProvisionalTaxYear2(i) & ", " & RespVars.ProvisionalTaxAmount2(i) & ")"
                                lngRecordsAffected = objAdoConn.executequery(strSql)
                                If lngRecordsAffected <> 1 Then
                                    'objAdoConn.Connection.RollbackTrans
                                    PerformDirectiveInsert2 = False
                                    Exit Function
                                Else
                                    PerformDirectiveInsert2 = True
                                End If
                            End If
                        End If
                        If RespVars.ProvisionalTaxAmount3(i) <> 0 Then
                            If RespVars.ProvisionalTaxAmount3(i) <> "NULL" Then
                                strSql = "Insert into DIR_PROVISIONAL_TAX "
                                strSql = strSql & " (REQ_SEQ_NUM, TAX_PERIOD, TAX_AMOUNT)"
                                strSql = strSql & " values(" & RespVars.ReqSeqNum(i) & ", " & RespVars.ProvisionalTaxYear3(i) & ", " & RespVars.ProvisionalTaxAmount3(i) & ")"
                                lngRecordsAffected = objAdoConn.executequery(strSql)
                                If lngRecordsAffected <> 1 Then
                                    'objAdoConn.Connection.RollbackTrans
                                    PerformDirectiveInsert2 = False
                                    Exit Function
                                Else
                                    PerformDirectiveInsert2 = True
                                End If
                            End If
                        End If
                        
                        'If UpdateDir_Request_FormsStatus(colRecordData(i)(0), colRecordData(i)(16), i) Then
                        If UpdateDir_Request_FormsStatus(RespVars.ReqSeqNum(i), RespVars.ItRefNo(i), i) Then
                            PerformDirectiveInsert2 = True
                            'objAdoConn.Connection.CommitTrans
                        Else
                            PerformDirectiveInsert2 = False
                            Exit Function
                        End If
                End If
            Else
                'if there's already a response for this seq no ignore it
                'but wait - for IRP3Bs, SARS send it away to Jhb from PTA
                'which means it takes at least and extra day to process
                'AND we have to phone them and bug them for it
                'AND in the meantime they send us a response with status code "06"
                'which we insert into the db and which we'll later need to
                'update/replace with the ACTUAL RESPONSE
                 If rsExistingReference!REQUEST_STATUS = "06" Then
                    strSql = "Delete from DIR_RESPONSE where REQ_SEQ_NUM = " & RespVars.ReqSeqNum(i) & " and REQUEST_STATUS = '06'"
                    lngRecordsAffected = objAdoConn.executequery(strSql)
                    intDeleteAttempt = intDeleteAttempt + 1
                    If lngRecordsAffected <> 1 Then
                        'objAdoConn.Connection.RollbackTrans
                        HandleError Err.Number, Err.Description, "PerformDirectiveInsert2", "Unable to delete the '06' record for this response in DIR_RESPONSE. Req_seq_num: " & RespVars.ReqSeqNum(i), i
                        PerformDirectiveInsert2 = False
                        Exit Function
                    Else
                        'retry insert?
                        If intDeleteAttempt <= 3 Then
                            GoTo InsertResponse
                        Else
                            HandleError Err.Number, Err.Description, "PerformDirectiveInsert2", "Unable to Insert the new response after deleting the '06' record for this response in DIR_RESPONSE. Req_seq_num: " & RespVars.ReqSeqNum(i), i
                            PerformDirectiveInsert2 = False
                            Exit Function
                        End If
                    End If
                    
                 Else
'                    'if it's not an "06" case, then ignore it
'                    'rather raise error
'                    HandleError Err.Number, Err.Description, "PerformDirectiveInsert2", "There is already a record for this response in DIR_RESPONSE. Req_seq_num: " & RespVars.ReqSeqNum(i), i
'                    PerformDirectiveInsert2 = False
'===============================================================================================================================================================================================================================================
                'New code added by Shane on 2002/12/17
                'If the directive response record already exists - write to temp table
                'This is to accomodate the fact that SARS keep on sending us duplicate dir response data
                        strSql = "Insert into DIR_RESPONSE_TEMP ("
                        strSql = strSql & " REQ_SEQ_NUM,"
                        strSql = strSql & " REQUEST_STATUS,"
                        strSql = strSql & " EMAILED_YN,"
                        strSql = strSql & " SARS_APPL_ID,"
                        strSql = strSql & " INCOME_TAX_AREA_CODE,"
                        strSql = strSql & " DIRECTIVE_NUMBER,"
                        'CHANGES AS PER SARS FOR 5/7/2001
                        'strSql = strSql & " DECLINE_REASON,"
                        strSql = strSql & " ISSUE_DATE,"
                        strSql = strSql & " DIR_START_DATE,"
                        strSql = strSql & " DIR_END_DATE,"
                        strSql = strSql & " TAX_PERCENTAGE,"
                        strSql = strSql & " TAX_FREE_PORTION,"
                        strSql = strSql & " GROSS_AMOUNT_PAYE,"
                        strSql = strSql & " DEDUCTION_FREQUENCY,"
                        'CHANGES AS PER SARS FOR 5/7/2001
                        strSql = strSql & " ALLOWED_CONTRIB,"
                        strSql = strSql & " VAT_REGISTRATION_NUMBER,"
                        strSql = strSql & " VAT_AMOUNT,"
                        strSql = strSql & " PAYE_AMT,"
                        strSql = strSql & " ASSESSED_TAX_AMT,"
                        strSql = strSql & " IT88_REF_NO,"
                        'CHANGES FOR SARS FOR 28/06/2002
                        strSql = strSql & " APPROVED_DEEMED_RENUMERATION,"
                        strSql = strSql & " SCOD_DIR_TYPE,"
                        strSql = strSql & " CREATED_DATE,"
                        strSql = strSql & " CREATED_BY,"
                         strSql = strSql & " RES_FILE_NAME)"
                        strSql = strSql & " values("
                        strSql = strSql & RespVars.ReqSeqNum(i) & ", "
                        strSql = strSql & RespVars.ReqStatus(i) & ", "
                        strSql = strSql & "'N', "
                        strSql = strSql & RespVars.ApplId(i) & ", "
                        strSql = strSql & RespVars.ItArea(i) & ", "
                        strSql = strSql & RespVars.DirId(i) & ", "
                        strSql = strSql & RespVars.IssueDate(i) & ", "
                        strSql = strSql & RespVars.ValidityStart(i) & ", "
                        strSql = strSql & RespVars.ValidityEnd(i) & ", "
                        strSql = strSql & RespVars.TaxRate(i) & ", "
                        strSql = strSql & RespVars.TaxFreePortion(i) & ", "
                        strSql = strSql & RespVars.GrossAmountPaye(i) & ", "
                        strSql = strSql & RespVars.DeductionFrequency(i) & ", "
                        strSql = strSql & RespVars.AllowedContributions(i) & ", "
                        strSql = strSql & RespVars.VatRegNo(i) & ", "
                        strSql = strSql & RespVars.VatAmount(i) & ", "
                        strSql = strSql & RespVars.PayeAmount(i) & ","
                        strSql = strSql & RespVars.AssessedTaxAmount(i) & ","
                        strSql = strSql & RespVars.It88RefNo(i) & ","
                        strSql = strSql & RespVars.ApprovedDeemedRenumeration(i) & ","
                        strSql = strSql & "'LS',"
                        strSql = strSql & " GETDATE(),"
                        strSql = strSql & " 'TkInsert',"
                        strSql = strSql & "'" & objReg.AsciiFileName & "')"
                        
                        'objAdoConn.Connection.BeginTrans
                        lngRecordsAffected = objAdoConn.executequery(strSql)
                        If lngRecordsAffected <> 1 Then
                            'objAdoConn.Connection.RollbackTrans
                            PerformDirectiveInsert2 = False
                            Exit Function
                        Else
                            PerformDirectiveInsert2 = True
                        End If
'===============================================================================================================================================================================================================================================
                End If
            'Shane: Code changed 27/03/2003
            'Take out this code so that the insert into the response file will still take place, even if there wasn't a request
            'This is to accomodate the fact that in a split second, a user could delete a request after it was sent to SARS if the two events
            'take place within seconds of each other
            'HandleError Err.Number, Err.Description, "PerformDirectiveInsert2", "No matching request for this response. Req_seq_num: " & RespVars.ReqSeqNum(i), i
            'PerformDirectiveInsert2 = False
            'Exit Function
            End If
        Else
            HandleError Err.Number, Err.Description, "PerformDirectiveInsert2", "No matching request for this response. Req_seq_num: " & RespVars.ReqSeqNum(i), i
            PerformDirectiveInsert2 = False
            Exit Function
        End If
    Else
        HandleError Err.Number, Err.Description, "PerformDirectiveInsert2", "No matching request for this response. Req_seq_num: " & RespVars.ReqSeqNum(i), i
        PerformDirectiveInsert2 = False
        Exit Function
    End If

        DoEvents
        'frmShanksOpt.pgbPercent.Value = (i / colRecordData.Count) * 100
        frmShanksOpt.pgbPercent.Value = (i / RespVars.FileResponseCount) * 100
        frmShanksOpt.Label1.Caption = "Inserting Response detail into database. Record:  " & i & " of : " & RespVars.FileResponseCount
        frmShanksOpt.Refresh
        
Next i
    
    Unload frmShanksOpt
    
    Exit Function
PerformDirectiveInsert2_Fix:
    HandleError Err.Number, Err.Description, "PerformDirectiveInsert2", "Failed to insert record.", i
    PerformDirectiveInsert2 = False
End Function
Private Sub ResetRecordVars()

    Set RespVars.ReqSeqNum = Nothing
    Set RespVars.ApplId = Nothing
    Set RespVars.ItArea = Nothing
    Set RespVars.ItRefNo = Nothing
    Set RespVars.DirId = Nothing
    Set RespVars.ReqStatus = Nothing
    Set RespVars.IssueDate = Nothing
    Set RespVars.ValidityStart = Nothing
    Set RespVars.ValidityEnd = Nothing
    Set RespVars.GrossAmount = Nothing
    Set RespVars.AccrualDate = Nothing
    Set RespVars.TaxRate = Nothing
    Set RespVars.TaxFreePortion = Nothing
    Set RespVars.GrossAmountPaye = Nothing
    Set RespVars.DeductionFrequency = Nothing
    Set RespVars.AllowedContributions = Nothing
    Set RespVars.ApprovedDeemedRenumeration = Nothing
    Set RespVars.It88RefNo = Nothing
    Set RespVars.AssessedTaxAmount = Nothing
    Set RespVars.ProvisionalTaxAmount1 = Nothing
    Set RespVars.ProvisionalTaxAmount2 = Nothing
    Set RespVars.ProvisionalTaxAmount3 = Nothing
    Set RespVars.ProvisionalTaxYear1 = Nothing
    Set RespVars.ProvisionalTaxYear2 = Nothing
    Set RespVars.ProvisionalTaxYear3 = Nothing
    Set RespVars.VatRegNo = Nothing
    Set RespVars.VatAmount = Nothing
    Set RespVars.PayeRefNo = Nothing
    Set RespVars.PayeAmount = Nothing


    Set RespVars.ReqSeqNum = New Collection
    Set RespVars.ApplId = New Collection
    Set RespVars.ItArea = New Collection
    Set RespVars.ItRefNo = New Collection
    Set RespVars.DirId = New Collection
    Set RespVars.ReqStatus = New Collection
    Set RespVars.IssueDate = New Collection
    Set RespVars.ValidityStart = New Collection
    Set RespVars.ValidityEnd = New Collection
    Set RespVars.GrossAmount = New Collection
    Set RespVars.AccrualDate = New Collection
    Set RespVars.TaxRate = New Collection
    Set RespVars.TaxFreePortion = New Collection
    Set RespVars.GrossAmountPaye = New Collection
    Set RespVars.DeductionFrequency = New Collection
    Set RespVars.AllowedContributions = New Collection
    Set RespVars.ApprovedDeemedRenumeration = New Collection
    Set RespVars.It88RefNo = New Collection
    Set RespVars.AssessedTaxAmount = New Collection
    Set RespVars.ProvisionalTaxAmount1 = New Collection
    Set RespVars.ProvisionalTaxAmount2 = New Collection
    Set RespVars.ProvisionalTaxAmount3 = New Collection
    Set RespVars.ProvisionalTaxYear1 = New Collection
    Set RespVars.ProvisionalTaxYear2 = New Collection
    Set RespVars.ProvisionalTaxYear3 = New Collection
    Set RespVars.VatRegNo = New Collection
    Set RespVars.VatAmount = New Collection
    Set RespVars.PayeRefNo = New Collection
    Set RespVars.PayeAmount = New Collection
    RespVars.FileResponseCount = 0
End Sub
